package org.charry.lib.database_utility;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Database utility, it's for commonly-used DML, for advanced feature, such as
 * transaction, please use getConnection() to get the database handler directly.
 * 
 * @version 0.0.3
 */
public final class DatabaseFactory {
	private static Log log = LogFactory.getLog(DatabaseFactory.class);
	private static DatabaseFactory databaseInstance = null;
	private Connection connection = null;
	private Statement sharedStatement = null;
	private String aliasName;
	private static Map<String, DatabaseFactory> databaseInstanceMap = new HashMap<String, DatabaseFactory>();

	/**
	 * Singleton, the public interface to retrieve the instance.
	 * 
	 * @param alias
	 *            database alias
	 * @return a shared database factory instance
	 */
	public static synchronized DatabaseFactory getInstance(final String alias) {
		Object obj = databaseInstanceMap.get(alias);
		if (obj == null) {
			databaseInstance = new DatabaseFactory(alias);
			log.info("create factory:" + alias);
			databaseInstanceMap.put(alias, databaseInstance);
		} else {
			databaseInstance = (DatabaseFactory) obj;
		}

		return databaseInstance;
	}

	/**
	 * Init the database factory.
	 * 
	 * @param alias
	 *            database alias
	 */
	private DatabaseFactory(final String alias) {
		createConnection(alias);
	}

	/**
	 * Init the database factory.
	 * 
	 * @param alias
	 *            database alias
	 */
	private void createConnection(final String alias) {
		DatabaseConfig config = new DatabaseConfig(alias);

		String url = config.getStrDbUrl();
		String user = config.getStrDbUser();
		String pwd = config.getStrDbPassword();
		String driverName = config.getStrDbDriver();

		while (true) {
			try {
				Class.forName(driverName);
				connection = DriverManager.getConnection(url, user, pwd);
				sharedStatement = connection.createStatement(
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} catch (SQLException e) {
				log.error("SQLException:" + e);
			} catch (ClassNotFoundException e) {
				log.error("ClassNotFoundException:" + e);
			} catch (Exception e) {
				log.error("failed to connect to database:" + e + url + user
						+ pwd);
			}

			if (connection == null) {
				log
						.error("failed to connect to db, sleep 5 secs, try again later"
								+ url + user + pwd);
				try {
					Thread.sleep(5000);
				} catch (Exception e) {
					logStackTrace(log, e);
				}
			} else
				break;
		}

		this.aliasName = alias;
	}

	/**
	 * Execute query and return the result set, it depends on a shared
	 * statement, will cause problem in complicated usage.
	 * 
	 * The client code need to close the result set explicitly.
	 * 
	 * @deprecated
	 * @param sql
	 *            sql string
	 * @return result set
	 */
	public ResultSet executeQuery(final String sql) {
		ResultSet rs = null;
		try {
			rs = sharedStatement.executeQuery(sql);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sql);
		} catch (Exception e) {
			log.error("alias:" + this.aliasName + ":" + sql);
			logStackTrace(log, e);
		}

		return rs;
	}

	/**
	 * A enhancement to executeQuery(...).
	 * 
	 * The client code must call its close() method.
	 * 
	 * @param sql
	 *            query string
	 * @return result set
	 */
	public ResultSetEx executeQueryEx(final String sql) {
		ResultSetEx rsEx = new ResultSetEx();
		ResultSet rs = null;
		Statement stmt = null;

		try {
			stmt = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);

			rsEx.setResultSet(rs);
			rsEx.setStatement(stmt);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sql);
		} catch (Exception e) {
			log.error("alias:" + this.aliasName + ":" + sql);
			logStackTrace(log, e);
		}

		return rsEx;
	}

	/**
	 * The client code must close the returned Statement and its corresponding
	 * ResultSet, or else, it'll cause the resource leaks.
	 * 
	 * @param sql
	 *            sql string
	 * @return prepared statement
	 */
	public PreparedStatement executePreparedQuery(final String sql) {
		PreparedStatement stmt = null;

		try {
			stmt = connection.prepareStatement(sql);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sql);
		} catch (Exception e) {
			log.error("alias:" + this.aliasName + ":" + sql);
			logStackTrace(log, e);
		}

		return stmt;
	}

	/**
	 * Execute the SQL, it depends on the shared statement. use it with care.
	 * 
	 * @deprecated
	 * @param sql
	 *            sql string
	 */
	public void executeUpdate(final String sql) {
		try {
			sharedStatement.executeUpdate(sql);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sql);
		} catch (Exception e) {
			log.error("alias:" + this.aliasName + ":" + sql);
			logStackTrace(log, e);
		}
	}

	/**
	 * Execute an SQL.
	 * 
	 * The client code must close the ResultSetEx explicitly.
	 * 
	 * @param sql
	 *            SQL string
	 * @return result set
	 */
	public ResultSetEx executeUpdateEx(final String sql) {
		ResultSetEx rsEx = new ResultSetEx();
		ResultSet rs = null;
		Statement stmt = null;

		try {
			stmt = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			stmt.executeUpdate(sql);

			rsEx.setResultSet(rs);
			rsEx.setStatement(stmt);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sql);
		} catch (Exception e) {
			logStackTrace(log, e);
		}

		return rsEx;
	}

	/**
	 * Execute sql in batch.
	 * 
	 * @param sqlList
	 *            list of the sql string
	 */
	public void executeUpdateBatch(final ArrayList<String> sqlList) {
		Statement stmt = null;
		try {
			boolean autoCommit = connection.getAutoCommit();
			connection.setAutoCommit(false);
			stmt = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);

			for (int i = 0; i < sqlList.size(); i++) {
				stmt.addBatch(sqlList.get(i));
			}

			stmt.executeBatch();
			connection.commit();
			connection.setAutoCommit(autoCommit);
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
			log.error("alias:" + this.aliasName + ":" + sqlList);
		} catch (Exception e) {
			log.error("alias:" + this.aliasName + ":" + sqlList);
			logStackTrace(log, e);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
				logStackTrace(log, e);
			}
		}
	}

	/**
	 * A function to dump the stack frame.
	 * 
	 * @param log
	 *            log4j instance
	 * @param e
	 *            exception instance
	 */
	private void logStackTrace(final Log log, final Exception e) {
		log.error(e);

		StackTraceElement[] s = e.getStackTrace();
		for (int i = 0; i < s.length; i++) {
			log.error(s[i]);
		}
	}

	/**
	 * Close the database connection.
	 */
	public void closeConnection() {
		try {
			if (sharedStatement != null)
				sharedStatement.close();
			sharedStatement = null;

			if (connection != null)
				connection.close();
			connection = null;

			databaseInstance = null;
		} catch (SQLException sqlexception) {
			log.error("SQLException:" + sqlexception);
		} catch (Exception e) {
			logStackTrace(log, e);
		}

		databaseInstanceMap.remove(aliasName);
		log.info("close connection, count of db instance:"
				+ databaseInstanceMap.size());
	}

	public boolean notFoundRecord(final String sql) {
		return !(foundRecord(sql));
	}

	/**
	 * Return the database connection.
	 * 
	 * DO NOT use this interface to close the connection, use closeConnection()
	 * instead
	 * 
	 * @return database connection
	 */
	public Connection getConnection() {
		return this.connection;
	}

	public boolean foundRecord(final String sql) {
		ResultSetEx rx = executeQueryEx(sql);
		boolean bFound = false;
		try {
			bFound = rx.getResultSet().first();
		} catch (SQLException e) {
			log.error("SQLException:" + e);
		} catch (Exception e) {
			logStackTrace(log, e);
		}

		rx.close();

		return bFound;
	}

	public static void main(String s[]) {

		ArrayList<String> list = new ArrayList<String>();
		list.add("insert into test values(2)");
		list.add("insert into test values12)");
		DatabaseFactory.getInstance("apple").executeUpdateBatch(list);

		String sql = "SELECT * FROM catv";
		ResultSetEx rx = DatabaseFactory.getInstance("apple").executeQueryEx(
				sql);
		try {
			while (rx.getResultSet().next()) {
				String a = rx.getResultSet().getString(2);
				System.out.println(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rx.close();
		}

		PreparedStatement ps = DatabaseFactory.getInstance("apple")
				.executePreparedQuery(sql);
	}

	/**
	 * A wrapper for MySQL ResultSet.
	 * 
	 * @author wcharry
	 * 
	 */
	public final class ResultSetEx {
		ResultSet resultSet = null;
		Statement statement = null;

		public ResultSet getResultSet() {
			return resultSet;
		}

		public void setResultSet(ResultSet resultSet) {
			this.resultSet = resultSet;
		}

		public Statement getStatement() {
			return statement;
		}

		public void setStatement(Statement statement) {
			this.statement = statement;
		}

		/**
		 * Close the resources, this function must be called by the client code.
		 */
		public void close() {
			try {
				if (resultSet != null)
					resultSet.close();

				if (statement != null)
					statement.close();
			} catch (Exception e) {
				log.error(e);
			}
		}
	}
}
